package com.jixiantubu.entity;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.gemptc.bean.Photo_Library;
import com.gemptc.bean.PostInfoBean;
import com.gemptc.utils.DBUtil;
import com.gemptc.utils.DataUtil;
import com.jixiantubu.factory.Post_InfoDao;

public class Post_InfoDaoimp implements Post_InfoDao {

	public List<PostInfoBean> selectIsfine(int pageNow) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<PostInfoBean> list = new ArrayList<PostInfoBean>();
		List<String> list2;
		PostInfoBean postInfoBean;
		int m = (pageNow - 1) * DataUtil.pageNum;
		int n = DataUtil.pageNum;
		String sql = "SELECT users.`user_id`,users.`user_name`,users.`user_nickname`,users.`user_imgPath`,post_info.`pi_title`, post_info.`pi_content`,post_info.`pi_createTime`, post_info.`pi_isFine`,  post_info.`pi_isTop`,  post_info.`pi_reply`,post_info.`pi_scan`,post_info.`pi_TopicID` FROM  users,  post_info WHERE users.`user_id` = post_info.`user_id`"
				+ " AND pi_isFine=TRUE ORDER BY post_info.`pi_createTime` DESC limit ?,?;";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, m);
			preparedStatement.setInt(2, n);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int userid = resultSet.getInt(1);
				String username = resultSet.getString(2);
				String usernickname = resultSet.getString(3);
				String userimg = resultSet.getString(4);
				String title = resultSet.getString(5);
				String content = resultSet.getString(6);
				String date = resultSet.getString(7);
				boolean isFine = resultSet.getBoolean(8);
				boolean isTop = resultSet.getBoolean(9);
				int reply = resultSet.getInt(10);
				int scan = resultSet.getInt(11);
				int post_info_TopicID = resultSet.getInt(12);
				int phid=selectPhid1(date);
				list2 = selectPhoto(userid, phid);
				postInfoBean = new PostInfoBean(userid, scan, reply, username,usernickname,
						title, content, date, userimg, isTop, isFine,
						post_info_TopicID, list2);
				list.add(postInfoBean);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;

	}

	@Override
	public boolean insert(int userid, String title, String content,
			String createTime,boolean a) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		boolean falg = false;
		String sql = "insert into post_info (user_id,pi_title,pi_content,pi_createTime,pi_hidden) values (?,?,?,?,?);";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, userid);
			preparedStatement.setString(2, title);
			preparedStatement.setString(3, content);
			preparedStatement.setString(4, createTime);
			preparedStatement.setBoolean(5, a);
			preparedStatement.execute();
			falg = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return falg;
	}

	@Override
	public List<PostInfoBean> select(int pageNow) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<PostInfoBean> list = new ArrayList<PostInfoBean>();
		List<String> list2;
		int m = (pageNow - 1) * DataUtil.pageNum;
		int n = DataUtil.pageNum;
		String sql = "SELECT users.`user_id`,users.`user_name`,users.`user_nickname`,"
				+ "users.`user_imgPath`,post_info.`pi_title`, post_info.`pi_content`,"
				+ "post_info.`pi_createTime`, post_info.`pi_isFine`,  post_info.`pi_isTop`,"
				+ "  post_info.`pi_reply`,post_info.`pi_scan`,post_info.`pi_TopicID` FROM "
				+ " users,  post_info WHERE users.`user_id` = post_info.`user_id`AND"
				+ " post_info.`pi_hidden`=FALSE  ORDER BY post_info.`pi_createTime` DESC "
				+ " limit ?,?;";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, m);
			preparedStatement.setInt(2, n);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int userid = resultSet.getInt(1);
				String username = resultSet.getString(2);
				String usernickname=resultSet.getString(3);
				String userimg = resultSet.getString(4);
				String title = resultSet.getString(5);
				String content = resultSet.getString(6);
				String date = resultSet.getString(7);
				boolean isFine = resultSet.getBoolean(8);
				boolean isTop = resultSet.getBoolean(9);
				int reply = resultSet.getInt(10);
				int scan = resultSet.getInt(11);
				int post_info_TopicID = resultSet.getInt(12);
				int phid=selectPhid1(date);
				list2=selectPhoto(userid, phid);
				//System.out.println(list2.toString());
				PostInfoBean postInfoBean = new PostInfoBean(userid, scan,
						reply, username, usernickname,title, content, date, userimg, isTop,
						isFine, post_info_TopicID, list2);
				list.add(postInfoBean);
				System.out.println(list.toString());
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return list;
	}
	
	private int selectPhid1(String date) {
		int phid = 0;
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql = "select ph_id from photo where ph_date=?";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, date);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				phid = resultSet.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return phid;
	}

	private List<String> selectPhoto(int userid, int phid) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<String> list = new ArrayList<String>();
		String sql = "SELECT pl_path FROM photo_library WHERE user_id=? AND ph_id=?;";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, userid);
			preparedStatement.setInt(2, phid);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				String pl_path = resultSet.getString(1);
				// Photo_library photo_library=new Photo_library(pl_path);
				list.add(pl_path);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return list;
	}

	@Override
	public int selectPhid(String date) {
		int phid = 0;
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql = "select ph_id from photo where ph_date=?";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, date);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				phid = resultSet.getInt(1);
				System.out.println("phid"+phid);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			 DBUtil.release(resultSet, preparedStatement, connection);
		}
		return phid;
	}

	@Override
	public boolean insertPhotoLibary(List<Photo_Library> list) {
		boolean falg = false;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		String sql = "INSERT INTO photo_library (user_id,ph_id,pl_path) VALUES(?,?,?)";
		try {
			connection = DBUtil.getConnection();
			for (int i = 0; i < list.size(); i++) {
				preparedStatement = connection.prepareStatement(sql);
				preparedStatement.setInt(1, list.get(i).getUser_id());
				preparedStatement.setInt(2, list.get(i).getPh_id());
				preparedStatement.setString(3, list.get(i).getPl_path());
				preparedStatement.execute();
			}
			falg = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(null, preparedStatement, connection);
		}
		return falg;
	}

	@Override
	public int selectTopicid(String date) {
		int topic = 0;
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql = "select pi_TopicID from post_info where pi_createTime=?";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, date);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				topic = resultSet.getInt(1);
				System.out.println(topic);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return topic;
	}

	@Override
	public boolean insertPhoto(int userid,int topicid,String date) {
		boolean falg=false;
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql="insert into photo(user_id,pi_TopicID,ph_date) values (?,?,?)";
		try {
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setInt(1, userid);
			preparedStatement.setInt(2, topicid);
			preparedStatement.setString(3, date);
			preparedStatement.execute();
			
			falg=true;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return falg;
	}
	@Override
	public boolean insertScan(int scan,int topicid) {
		boolean falg=false;
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql="UPDATE  post_info SET pi_scan=? WHERE pi_TopicID= ? ;";
		try {
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setInt(1, scan);
			preparedStatement.setInt(2, topicid);
			preparedStatement.execute();
			falg=true;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return falg;
		
	}
		//我的帖子信息
			public List<PostInfoBean> selectpost(int user_id) {
				Connection connection = DBUtil.getConnection();
				PreparedStatement preparedStatement = null;
				ResultSet resultSet = null;
				List<PostInfoBean> list = new ArrayList<PostInfoBean>();
				List<String> list2;
				
				String sql = "SELECT users.`user_id`,users.`user_nickname`," +
						"users.`user_imgPath`,post_info.`pi_title`," +
						" post_info.`pi_content`,post_info.`pi_createTime`, post_info.`pi_isFine`,  " +
						"post_info.`pi_isTop`,  post_info.`pi_reply`,post_info.`pi_scan`," +
						"post_info.`pi_TopicID` " +
						"FROM  users,  post_info WHERE users.`user_id` = post_info.`user_id` " +
						"AND users.`user_id`=?;";
				try {
					preparedStatement = connection.prepareStatement(sql);
					preparedStatement.setInt(1, user_id);
					
					resultSet = preparedStatement.executeQuery();
					while (resultSet.next()) {
						int userid = resultSet.getInt(1);
						String user_nickname = resultSet.getString(2);
						String userimg = resultSet.getString(3);
						String title = resultSet.getString(4);
						String content = resultSet.getString(5);
						String date = resultSet.getString(6);
						boolean isFine = resultSet.getBoolean(7);
						boolean isTop = resultSet.getBoolean(8);
						int reply = resultSet.getInt(9);
						int scan = resultSet.getInt(10);
						int post_info_TopicID = resultSet.getInt(11);
						int phid=selectPhid1(date);
						list2=selectPhoto(userid, phid);
						System.out.println(list2.toString());
						PostInfoBean postInfoBean =					
							new PostInfoBean(userid,scan, reply, null,
									user_nickname, title, content, date,
									userimg, isTop, isFine, post_info_TopicID,list2 );
						list.add(postInfoBean);
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					DBUtil.release(resultSet, preparedStatement, connection);
				}
				return list;
			}
			
			//115查找我收藏的帖子
			public List<PostInfoBean> selectcollectpost(int user_id) {
				Connection connection = DBUtil.getConnection();
				PreparedStatement preparedStatement = null;
				ResultSet resultSet = null;
				List<PostInfoBean> list = new ArrayList<PostInfoBean>();
				List<String> list2;
				
				String sql = "SELECT users.`user_id`,users.`user_nickname`," +
						"users.`user_imgPath`,post_info.`pi_title`," +
						" post_info.`pi_content`,post_info.`pi_createTime`, post_info.`pi_isFine`,  " +
						"post_info.`pi_isTop`,  post_info.`pi_reply`,post_info.`pi_scan`," +
						"post_info.`pi_TopicID` " +
						"FROM  collect,  post_info,users WHERE collect.`pi_TopicID` = post_info.`pi_TopicID` " +
						"AND users.`user_id`=post_info.`user_id` "
						+ "AND collect.`user_id`=?;";
				try {
					preparedStatement = connection.prepareStatement(sql);
					preparedStatement.setInt(1, user_id);
					
					resultSet = preparedStatement.executeQuery();
					while (resultSet.next()) {
						int userid = resultSet.getInt(1);
						String user_nickname = resultSet.getString(2);
						String userimg = resultSet.getString(3);
						String title = resultSet.getString(4);
						String content = resultSet.getString(5);
						String date = resultSet.getString(6);
						boolean isFine = resultSet.getBoolean(7);
						boolean isTop = resultSet.getBoolean(8);
						int reply = resultSet.getInt(9);
						int scan = resultSet.getInt(10);
						int post_info_TopicID = resultSet.getInt(11);
						int phid=selectPhid1(date);
						list2=selectPhoto(userid, phid);
						System.out.println(list2.toString());
						PostInfoBean postInfoBean =					
							new PostInfoBean(userid,scan, reply, null,
									user_nickname, title, content, date,
									userimg, isTop, isFine, post_info_TopicID,list2 );
						list.add(postInfoBean);
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					DBUtil.release(resultSet, preparedStatement, connection);
				}
				return list;
			}
			

}
